package db2

import (
	//"fmt"
	"fmt"
	"strconv"
	"strings"

	"github.com/gohouse/gorose/v2"
	formatter "github.com/pieterclaerhout/go-formatter"
	//log "github.com/sirupsen/logrus"
)

//获取数据库对象
func GetObjects() ([]gorose.Data, error) {

	sql := `
	
	SELECT
 trim( T. NAME)  OBJECTNAME ,
   'TABLE' OBJECTTYPE ,
  trim(T.  CREATOR ) OBJECTSCEMA ,
  null as DDL,
  T.CTIME CREATETIME,
  T.ALTER_TIME UPDATETIME,
  trim(T.REMARKS)  remark
FROM
     SYSIBM.SYSTABLES T
    where T.CREATOR not like 'SYS%'
    and T.type='T'
    union all

SELECT
trim( V. NAME)  OBJECTNAME ,
   'VIEW' OBJECTTYPE ,
trim(  V.  CREATOR ) OBJECTSCEMA ,
  V. TEXT as DDL,
  T.CTIME CREATETIME,
  T.ALTER_TIME UPDATETIME,
  null remark
FROM
    SYSIBM.SYSVIEWS V inner join   SYSIBM.SYSTABLES T on t.NAME =V.NAME  and t.CREATOR =v.CREATOR and t.type='V'
    where V.CREATOR not like 'SYS%'



    union all
   select
   trim( P.SPECIFICNAME) OBJECTNAME ,
    'PROCEDURE' OBJECTTYPE ,
   trim(P.PROCSCHEMA)  OBJECTSCEMA,
    p.text ddl,
  p.CREATE_TIME CREATETIME,
  p.CREATE_TIME UPDATETIME,
  null remark


    from  SYSCAT.PROCEDURES P
    where P.PROCSCHEMA not like 'SYS%' and P.PROCSCHEMA !='SQLJ'

    union all

      select 
      trim( P.SPECIFICNAME) OBJECTNAME ,
    'FUNCTION' OBJECTTYPE ,
    trim(P.FUNCSCHEMA) OBJECTSCEMA,
    p.body ddl,
  p.CREATE_TIME CREATETIME,
  p.CREATE_TIME UPDATETIME,
  null remark


    from  SYSCAT.FUNCTIONS P
    where P.FUNCSCHEMA not like 'SYS%' and P.FUNCSCHEMA !='SQLJ'
    
        union all

      select 
      trim(T.TRIGNAME) OBJECTNAME ,
    'TRIGGER' OBJECTTYPE ,
    trim(T.TRIGSCHEMA) OBJECTSCEMA,
    T.TEXT ddl,
  T.CREATE_TIME CREATETIME,
  T.CREATE_TIME UPDATETIME,
  null remark

    from  SYSCAT.TRIGGERS T
    where T.TRIGSCHEMA not like 'SYS%' and T.TRIGSCHEMA !='SQLJ'
	`

	list, err := DB().Query(sql)

	if err != nil {
		return nil, err
	}

	for _, d := range list {
		d["ID"] = d["OBJECTTYPE"].(string) + "-" + d["OBJECTSCEMA"].(string) + "-" + d["OBJECTNAME"].(string)
		OBJECTTYPE, ok := d["OBJECTTYPE"]
		if ok && OBJECTTYPE == "TABLE" {
			columns, err := GetColumns(d["OBJECTSCEMA"].(string), d["OBJECTNAME"].(string))
			//拼接建表DDL
			ddl := "CREATE TABLE " + d["OBJECTSCEMA"].(string) + "." + d["OBJECTNAME"].(string) + "("
			//拼接注释ddl
			ddl_comment := ""

			tableremark, ok := d["REMARK"]
			if ok && tableremark != nil {
				ddl_comment = ddl_comment + "COMMENT ON TABLE " + d["OBJECTSCEMA"].(string) + "." + d["OBJECTNAME"].(string) + "  is '" + tableremark.(string) + "'   ;"
			}

			//拼接主键ddl
			ddl_create := ""
			//主键列表
			primaryKeys := make([]string, 0)

			if err == nil {
				for colindex, col := range columns {
					ddl = ddl + col["COLUMNNAME"].(string) + " "
					coltype := col["TYPE"].(string)

					if coltype == "VARCHAR" || coltype == "CHARACTER" || coltype == "CLOB" || coltype == "BLOB" {
						ddl = ddl + "(" + strconv.FormatInt(int64(col["LENGTH"].(int32)), 10) + ")"
					} else if coltype == "DECIMAL" {
						ddl = ddl + "(" + strconv.FormatInt(int64(col["LENGTH"].(int32)), 10) + "," + strconv.FormatInt(int64(col["SCALE"].(int32)), 10) + ")"

					}

					if colindex != len(columns)-1 {
						ddl = ddl + ","
					}

					remark, ok := col["REMARK"]
					if ok && remark != nil {
						remark_s := remark.(string)
						if remark_s != "" {
							ddl_comment = ddl_comment + "COMMENT ON COLUMN " + d["OBJECTSCEMA"].(string) + "." + d["OBJECTNAME"].(string) + "." + col["COLUMNNAME"].(string) + "  is '" + remark_s + "'   ;"
						}

					}
					//是否主键
					ISPRIMARYKEY, ok := col["ISPRIMARYKEY"]
					if ok && ISPRIMARYKEY != nil && ISPRIMARYKEY.(int32) == 1 {
						primaryKeys = append(primaryKeys, col["COLUMNNAME"].(string))
					}

				}

				if len(primaryKeys) > 0 {
					ddl_create = ddl_create + ", PRIMARY KEY ("
					ddl_create = ddl_create + strings.Join(primaryKeys, ",")
					ddl_create = ddl_create + ")"
				}

				ddl_create = ddl_create + ");"

				ddl = ddl + ddl_create
				ddl = ddl + ddl_comment

			}
			//fmt.Println("输出DDL:")
			//fmt.Println(ddl)
			formatddl, err := formatter.SQL(ddl)
			if err == nil {
				d["DDL"] = formatddl
			} else {
				d["DDL"] = ddl
			}
		}
	}

	return list, nil
}

//取得表的列属性列表
func GetColumns(scema string, tablename string) ([]gorose.Data, error) {
	sql := fmt.Sprintf(`
   SELECT
   trim(TABSCHEMA) SCHEMA,
   trim( TABNAME) tablename,
   trim( COLNAME) columnname,
   COLNO index,
   TYPENAME type,
   LENGTH,
   SCALE,
   DEFAULT defaultvalue,
   case when NULLS='Y' then 1 else 0 end  nullable ,
   KEYSEQ isprimarykey ,
   trim(left(trim(REMARKS),120)) REMARK
FROM
   SYSCAT.COLUMNS
   where TABSCHEMA ='%s'  and TABNAME ='%s'
   order by  colno  
   `, scema, tablename)
	//fmt.Println(sql)
	list, err := DB().Query(sql)

	if err != nil {
		return nil, err
	}
	return list, nil
}

//模式列表
func GetSchemaList() ([]gorose.Data, error) {
	sql := `
  SELECT
  trim(NAME ) id ,trim(NAME ) NAME,'SCEMA' type ,trim(NAME )  scema
FROM
SYSIBM.SYSSCHEMATA
  where DEFINERTYPE ='U'
   `
	list, err := DB().Query(sql)

	if err != nil {
		return nil, err
	}
	return list, nil
}
